IF OBJECT_ID('dbo.RptPCROTurnaroundDetailReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROTurnaroundDetailReport
    IF OBJECT_ID('dbo.RptPCROTurnaroundDetailReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROTurnaroundDetailReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROTurnaroundDetailReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPCROTurnaroundDetailReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 09/03/2008
--
-- Description: Source of Report, "PCROTurnaroundDetailReport"
--                  In Insight DB Reporting Services
--
-- Serves as a drill down to the Report, PCROTurnaroundReport
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptPCROTurnaroundDetailReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 09/03/08    KC        Created
-- 09/05/08    KC        Almost done, but changed CTA to Trial in search.
-- 09/08/08    KC        Added flag for only returning some counts
-- 09/09/08    KC        Added some fields for drilldown report
-- 09/10/08    KC        Spec changes on Defns of Groups.
-- 09/18/08    KC        Fixed Grouping Issue
-- 09/24/08    KC        Drilldown wasn't working right without fix to 
--                       SubAnalysisType Filter.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptPCROTurnaroundDetailReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @AnalysisType                      VARCHAR(20) = NULL,
     @SubAnalysisType                   VARCHAR(20) = NULL,
     @ManagerType                       VARCHAR(20) = NULL,
     @ManagerId                         INT=NULL,
     @EndCount                          INT=NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @REPORTCAT_LIST     VARCHAR(50),
               @StartCount         INT

     --'REPORTCAT_19',     -- CSCR - Contract Deliverable
     --'REPORTCAT_33',     -- CSCR - Amendment
     --'REPORTCAT_30',     -- CSCR - Working Budget
     --'REPORTCAT_42',     -- CSCR - Working Budget Amendment
     --'REPORTCAT_41')     -- CSCR - MCA
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @AnalysisType IN ('ALL','','0') SET @AnalysisType = NULL
     IF @SubAnalysisType IN ('ALL','','0') SET @SubAnalysisType = NULL
     IF @ManagerType NOT IN ('PreAwardManager','CSCRCaseManager') SET @ManagerType = 'PreAwardManager'
     IF @ManagerId IN (0,NULL) SET @ManagerId = NULL
     
     -- We have to combine Contracts and Working Budgets to include Amendments of each type
     IF @AnalysisType = 'WorkingBudget' SET @REPORTCAT_LIST = 'REPORTCAT_30,REPORTCAT_42'
     IF @AnalysisType = 'Contract' SET @REPORTCAT_LIST = 'REPORTCAT_19,REPORTCAT_33'
     IF @AnalysisType = 'MCA' SET @REPORTCAT_LIST = 'REPORTCAT_41'
     IF @AnalysisType IS NULL SET @REPORTCAT_LIST = 'REPORTCAT_30,REPORTCAT_42,REPORTCAT_19,REPORTCAT_33,REPORTCAT_41'
     
     -- This proc is used by the summary report to return all counts, and by the detail (drill-down) report
     -- to display the raw data. If @EndCount is null, get all data, otherwise get those whose days fall between
     -- the following ranges
     IF @EndCount NOT IN (NULL,-1,30,45,60,89,90)
     BEGIN
          RAISERROR ('Error! @EndCount must be either NULL,30,45,60,89 or 90.',11,1)
          RETURN -1
     END
     
     IF @EndCount = -1 SET @EndCount = NULL
     
     -- Set Ranges
     IF @EndCount IS NOT NULL 
          SELECT @StartCount = CASE @EndCount 
                                   WHEN 30 THEN 0
                                   WHEN 45 THEN 31
                                   WHEN 60 THEN 46
                                   WHEN 89 THEN 61
                                   ELSE 90
                               END,
                 @EndCount = CASE WHEN @EndCount = 90 THEN 10000
                                   ELSE @EndCount
                              END     
         
     -- Return Result Set
     IF @AnalysisType != 'PROPOSAL' OR @AnalysisType IS NULL
     
     -- Deliverable Search
     SELECT    AI.AgreementId,
               PROP.FolderNumber AS FolderNumber,
               CPL.prop_no AS InfoEdPropNumber,
               @ManagerType AS ManagerType,
               CASE @ManagerType 
                    WHEN 'CSCRCaseManager' THEN AI.CSCRManagerId
                    ELSE AI.PreAwardManagerId
               END AS ManagerId,
               CASE @ManagerType 
                    WHEN 'CSCRCaseManager' THEN AI.CSCRCaseManagerName
                    ELSE AI.PreAwardManagerName
               END AS ManagerName,
               CPL.GroupCode AS GroupCode,
               CPL.REPORTCAT_CODE AS AnalysisTypeCode,
               CPL.REPORTCAT AS AnalysisType,
               CASE WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_42','REPORTCAT_33') THEN 'Amendments'
                    WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_30','REPORTCAT_19') AND 
                         RTRIM(AI.SubActivityType) IN ('Trial - Company Initiated (C)',
                                                       'Trial - Jointly Initiated (C)',
                                                       'Trial - Other (C)',
                                                       'Trial - PI Initiated (C)') THEN 'CTA'
                    WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_30','REPORTCAT_42','REPORTCAT_19','REPORTCAT_33') AND 
                         RTRIM(AI.SubActivityType) IN ('Support Agreement - Company Initiated (C)',
                                                       'Support Agreement - Jointly Initiated (C)',
                                                       'Support Agreement - PI Initiated (C)') THEN 'Support Agreement'
                    WHEN CPL.REPORTCAT_CODE = 'REPORTCAT_41' THEN 'MCA'
                    ELSE 'OTHER'
               END AS SubAnalysisType,
               AI.SubActivityType,
               CPL.ReportName AS ProposalOrDeliverableName,
               AI.PrincipalInvestigator,
               AI.SponsorName AS PrimarySponsor,
               AI.SecondarySponsor,
               AI.AgreementType,
               AI.TransactionType,
               AI.ProposalShortTitle AS Title,
               AI.Institution,
               AI.Department,
               CAST(CPL.ReportId AS VARCHAR(20)) AS ProposalOrDeliverableID,
               dbo.fnRemoveTime(CPL.CompletedDate) AS CompletedDate,
               dbo.fnRemoveTime(SUB.CreateDate) AS PendingDate,
               DATEDIFF(DD,SUB.CreateDate,CPL.CompletedDate) as DaysToComplete
     FROM      -- Get First Deliverable CSCR - Completed Date
               (
               SELECT    D.prop_no,
                         D.ReportId, 
                         D.REPORTCAT,
                         D.REPORTCAT_CODE,
                         -- Group CSCR Amendments with Contracts, and CSCR Working Budget Amendments with Working Budgets
                         CASE WHEN D.REPORTCAT_CODE = 'REPORTCAT_33' THEN 'CSCR - Contract'
                              WHEN D.REPORTCAT_CODE = 'REPORTCAT_42' THEN 'CSCR - Working Budget'
                              ELSE D.REPORTCAT 
                         END AS GroupCode,
                         D.ReportName,
                         MIN(D.ProcessedDate) AS CompletedDate
               FROM      dbo.vwInfoEdCSCRDeliverablesAll D 
               WHERE     D.REPORTCAT_CODE IN ('REPORTCAT_19','REPORTCAT_41','REPORTCAT_30','REPORTCAT_42','REPORTCAT_33') AND
                         D.REPORTSTAT_CODE = 'REPORTSTAT_13'     -- 'CSCR - Completed'
               GROUP BY  D.prop_no,
                         D.ReportId,
                         D.REPORTCAT,
                         D.REPORTCAT_CODE,
                         D.ReportName) CPL   
               -- Also need the first Pending Status Date
               INNER JOIN 
               (
               SELECT    D.ReportId, MIN(D.ProcessedDate) AS CreateDate
               FROM      dbo.vwInfoEdCSCRDeliverablesAll D 
               WHERE     D.REPORTCAT_CODE IN ('REPORTCAT_19','REPORTCAT_41','REPORTCAT_30','REPORTCAT_42','REPORTCAT_33') AND
                         D.REPORTSTAT_CODE = 'REPORTSTAT_61'     -- Pending
               GROUP BY  D.ReportId ) SUB ON CPL.ReportId = SUB.ReportId
               
               -- Some Deliverables are linked to Amendment Proposals, so we need to get the original
               -- FolderNumber
               INNER JOIN 
               (SELECT   DISTINCT 
                         CASE WHEN CHARINDEX('-',P.inst_no) > 0 
                                 THEN SUBSTRING(P.inst_no,1,CHARINDEX('-',P.inst_no) - 1) 
                                 ELSE P.inst_no 
                         END AS FolderNumber,
                         P.prop_no
               FROM      dbo.vwInfoEdProposalsSrLeader P) PROP ON CPL.prop_no = PROP.prop_no
               
               -- Get Master Agreement Information
               INNER JOIN dbo.AgrAgreement_Info AI ON PROP.FolderNumber = AI.FolderNumber
               
     WHERE     CPL.CompletedDate BETWEEN @StartDate AND @EndDate AND
               
               -- Analysis type Restriction
               (@AnalysisType IS NULL OR CPL.REPORTCAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@REPORTCAT_LIST))) AND
                              
               -- Further Restriction for different Types....
               (CASE WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_30','REPORTCAT_42','REPORTCAT_19','REPORTCAT_33') AND 
                         RTRIM(AI.SubActivityType) IN ('Trial - Company Initiated (C)',
                                                       'Trial - Jointly Initiated (C)',
                                                       'Trial - Other (C)',
                                                       'Trial - PI Initiated (C)') THEN 'CTA'
                    WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_30','REPORTCAT_42','REPORTCAT_19','REPORTCAT_33') AND 
                         RTRIM(AI.SubActivityType) IN ('Support Agreement - Company Initiated (C)',
                                                       'Support Agreement - Jointly Initiated (C)',
                                                       'Support Agreement - PI Initiated (C)') THEN 'Support Agreement'
                    WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_42','REPORTCAT_33') THEN 'Amendments'
                    WHEN CPL.REPORTCAT_CODE = 'REPORTCAT_41' THEN 'MCA'
                    ELSE 'OTHER'
               END) != 'OTHER' AND               
               
               -- Search for one Manager?
               (@ManagerId  IS NULL OR @ManagerId = CASE @ManagerType 
                                                       WHEN 'CSCRCaseManager' THEN AI.CSCRManagerId
                                                       ELSE AI.PreAwardManagerId
                                                  END ) AND
               
               ((@ManagerType = 'PreAwardManager' AND AI.PreAWardManagerName IS NOT NULL) OR
               (@ManagerType = 'CSCRCaseManager' AND AI.CSCRCaseManagerName IS NOT NULL)) AND
               
               -- Search for SubAnalysis type?
               (@SubAnalysisType IS NULL OR 
               @SubAnalysisType =  CASE WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_42','REPORTCAT_33') THEN 'Amendments'
                                        WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_30','REPORTCAT_19') AND 
                                             RTRIM(AI.SubActivityType) IN ('Trial - Company Initiated (C)',
                                                                           'Trial - Jointly Initiated (C)',
                                                                           'Trial - Other (C)',
                                                                           'Trial - PI Initiated (C)') THEN 'CTA'
                                        WHEN CPL.REPORTCAT_CODE IN ('REPORTCAT_30','REPORTCAT_42','REPORTCAT_19','REPORTCAT_33') AND 
                                             RTRIM(AI.SubActivityType) IN ('Support Agreement - Company Initiated (C)',
                                                                           'Support Agreement - Jointly Initiated (C)',
                                                                           'Support Agreement - PI Initiated (C)') THEN 'Support Agreement'
                                        WHEN CPL.REPORTCAT_CODE = 'REPORTCAT_41' THEN 'MCA'
                                        ELSE 'OTHER'
                                   END) AND
               
               -- Institution and Dept Search
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
     
               -- Proc serves as drilldown and source for Summary Report, check to see if we return all data.
               (@EndCount IS NULL OR DATEDIFF(DD,SUB.CreateDate,CPL.CompletedDate) BETWEEN @StartCount AND @EndCount)
               
     ORDER BY  ManagerName, CPL.CompletedDate ASC     

     ELSE      -- PROPOSAL Search...
     SELECT    AI.AgreementId,
               AI.FolderNumber AS FolderNumber,
               CPL.prop_no AS InfoEdPropNumber,
               @ManagerType AS ManagerType,
               CASE @ManagerType 
                    WHEN 'CSCRCaseManager' THEN AI.CSCRManagerId
                    ELSE AI.PreAwardManagerId
               END AS ManagerId,
               CASE @ManagerType 
                    WHEN 'CSCRCaseManager' THEN AI.CSCRCaseManagerName
                    ELSE AI.PreAwardManagerName
               END AS ManagerName,
               @AnalysisType AS GroupCode,
               CPL.TRANTYPE AS AnalysisTypeCode,
               @AnalysisType AS AnalysisType,
               CASE WHEN RTRIM(CPL.TRANTYPE) = 'CSCR-Amendment' THEN 'Amendments'
                    WHEN RTRIM(AI.SubActivityType) IN ('Trial - Company Initiated (C)',
                                                       'Trial - Jointly Initiated (C)',
                                                       'Trial - Other (C)',
                                                       'Trial - PI Initiated (C)') THEN 'CTA' 
                    WHEN RTRIM(AI.SubActivityType) IN ('Support Agreement - Company Initiated (C)',
                                                       'Support Agreement - Jointly Initiated (C)',
                                                       'Support Agreement - PI Initiated (C)') THEN 'Support Agreement'
                    ELSE 'OTHER'
               END AS SubAnalysisType,
               AI.SubActivityType,
               CPL.ReportName AS ProposalOrDeliverableName,
               AI.PrincipalInvestigator,
               AI.SponsorName AS PrimarySponsor,
               AI.SecondarySponsor,
               AI.AgreementType,
               AI.TransactionType,
               AI.ProposalShortTitle AS Title,
               AI.Institution,
               AI.Department,
               CAST(CPL.inst_no AS VARCHAR(20)) AS ProposalOrDeliverableID,
               dbo.fnRemoveTime(CPL.PendingSigDate) AS CompletedDate,
               dbo.fnRemoveTime(SUB.PendingDate) AS PendingDate,
               DATEDIFF(DD,SUB.PendingDate,CPL.PendingSigDate) as DaysToComplete
     FROM      -- Get first Pending Signature Date
               (SELECT   CASE WHEN CHARINDEX('-',P.inst_no) > 0 
                             THEN SUBSTRING(P.inst_no,1,CHARINDEX('-',P.inst_no) - 1) 
                             ELSE P.inst_no 
                         END AS FolderNumber,
                         P.prop_no,
                         P.inst_no, 
                         P.TransactionType AS TRANTYPE,
                         P.instrum AS INSTRUM,
                         P.short_title AS ReportName,
                         MIN(P.PropStatDate) AS PendingSigDate
               FROM      dbo.vwInfoEdProposalsSrLeader P 
               WHERE     (TransactionType LIKE '%Amendment%' OR TransactionType LIKE '%Master%') AND
                         P.prop_stat = 'PARTNERS_P_PROP_STAT_65'  -- CSCR Pending Signature
               GROUP BY  P.prop_no,
                         P.inst_no, 
                         P.TransactionType,
                         P.instrum,
                         P.short_title) CPL    
               --  Get first Pending Date        
               INNER JOIN 
               (
               SELECT    P.prop_no, 
                         MIN(P.PropStatDate) AS PendingDate
               FROM      dbo.vwInfoEdProposalsSrLeader P 
               WHERE     (TransactionType LIKE '%Amendment%' OR TransactionType LIKE '%Master%') AND
                         P.prop_stat IN ('PARTNERS_PD_PROP_STAT_PN','PARTNERS_P_BUD_STAT_1')  -- Pending
               GROUP BY  P.prop_no ) SUB ON CPL.prop_no = SUB.prop_no

               -- Get Master Agreement Information
               INNER JOIN dbo.AgrAgreement_Info AI ON CPL.FolderNumber = AI.FolderNumber
     WHERE      CPL.PendingSigDate BETWEEN @StartDate AND @EndDate AND
     
               -- Analysis type Restriction
               (CASE WHEN RTRIM(CPL.TRANTYPE) = 'CSCR-Amendment' THEN 'Amendments'
                    WHEN RTRIM(AI.SubActivityType) IN ('Trial - Company Initiated (C)',
                                                       'Trial - Jointly Initiated (C)',
                                                       'Trial - Other (C)',
                                                       'Trial - PI Initiated (C)') THEN 'CTA' 
                    WHEN RTRIM(AI.SubActivityType) IN ('Support Agreement - Company Initiated (C)',
                                                       'Support Agreement - Jointly Initiated (C)',
                                                       'Support Agreement - PI Initiated (C)') THEN 'Support Agreement'
                    ELSE 'OTHER'
               END) != 'OTHER' AND
               
               -- Search for one Manager?
               (@ManagerId  IS NULL OR @ManagerId = CASE @ManagerType 
                                                       WHEN 'CSCRCaseManager' THEN AI.CSCRManagerId
                                                       ELSE AI.PreAwardManagerId
                                                  END ) AND
               
               ((@ManagerType = 'PreAwardManager' AND AI.PreAWardManagerName IS NOT NULL) OR
               (@ManagerType = 'CSCRCaseManager' AND AI.CSCRCaseManagerName IS NOT NULL)) AND
               
               -- Search for SubAnalysis type?
               (@SubAnalysisType IS NULL OR 
               @SubAnalysisType =  CASE WHEN RTRIM(CPL.TRANTYPE) = 'CSCR-Amendment' THEN 'Amendments'
                                        WHEN RTRIM(AI.SubActivityType) IN ('Trial - Company Initiated (C)',
                                                                           'Trial - Jointly Initiated (C)',
                                                                           'Trial - Other (C)',
                                                                           'Trial - PI Initiated (C)') THEN 'CTA' 
                                        WHEN RTRIM(AI.SubActivityType) IN ('Support Agreement - Company Initiated (C)',
                                                                           'Support Agreement - Jointly Initiated (C)',
                                                                           'Support Agreement - PI Initiated (C)') THEN 'Support Agreement'
                                        ELSE 'OTHER'
                                   END) AND
               
               -- Institution and Dept Search
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
     
               -- Proc serves as drilldown and source for Summary Report, check to see if we return all data.
               (@EndCount IS NULL OR DATEDIFF(DD,SUB.PendingDate,CPL.PendingSigDate) BETWEEN @StartCount AND @EndCount)
     ORDER BY  ManagerName, CPL.PendingSigDate ASC     
     

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          RETURN -1
     END
     
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptPCROTurnaroundDetailReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROTurnaroundDetailReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROTurnaroundDetailReport >>>'
go

GRANT EXECUTE ON dbo.RptPCROTurnaroundDetailReport TO InsightReport,Insight
go


